package de.voichie.android.spike.database;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class MyDBAdapter
{
    int id = 0;
    public static final String KEY_ROWID = "_id";
    public static final String KEY_QUOTE = "Quote";
    private static final String TAG = "DBAdapter";
 
    private static final String DATABASE_NAME = "Random2";
    private static final int DATABASE_VERSION = 1;
 
    private static final String DATABASE_CREATE =
        "create table movie_information2 (movieTitle TEXT primary key, moreMovieInfoURL TEXT);";
    
    private final Context context;
 
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;
    
    public MyDBAdapter(Context ctx)
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

 private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context)
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion,
                              int newVersion)
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion
                  + " to "
                  + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS tblRandomQuotes");
            onCreate(db);
        }
    }
    
//---opens the database---
 public MyDBAdapter open() throws SQLException
 {
     db = DBHelper.getWritableDatabase();
     return this;
 }

 //---closes the database---
 public void close()
 {
     DBHelper.close();
 }

//---insert a title into the database---
 public long insertQuote(String Quote)
 {
	 SQLiteStatement stmtInsert = db.compileStatement("insert into movie_information2 "+  "(movieTitle,moreMovieInfoURL) "+  "values (?,?)"  );
		 stmtInsert.bindString(1,"Blade 2");
		 stmtInsert.bindString(2,"www.web.de");
	return stmtInsert.executeInsert();
 }
 
 public String getCountOfRows () {
	 SQLiteStatement stmtInsert = db.compileStatement("SELECT COUNT(movieTitle) FROM movie_information2");
	 return stmtInsert.simpleQueryForString();
 }
 
 
 public String getMovieURLForTitleRow (String movieTitle) {
	 
	 SQLiteStatement stmtInsert = db.compileStatement("SELECT moreMovieInfoURL FROM movie_information2 WHERE movieTitle = " + "'" + movieTitle + "'");
	 return stmtInsert.simpleQueryForString();
	 
//	 Alternative
//	 Cursor cursor = db.rawQuery(
//             "SELECT moreMovieInfoURL FROM movie_information2 WHERE movieTitle = " + "'" + movieTitle + "'", null);
//         if(cursor.moveToFirst()) {
//             return cursor.getString(0);
//         }
//         return cursor.getString(0);
 }
 
 public String getMovieTitle (int indexRow) {
	 SQLiteStatement stmtInsert = db.compileStatement("SELECT movieTitle FROM movie_information2 WHERE rowid="+ indexRow + ";");
	 return stmtInsert.simpleQueryForString();
 }
 
 public String getMoreMovieURL (int indexRow) {
	 SQLiteStatement stmtInsert = db.compileStatement("SELECT moreMovieInfoURL FROM movie_information2 WHERE rowid="+ indexRow + ";");
	 return stmtInsert.simpleQueryForString();
 }
 
 public String getRowString (int indexRow) {
	 return "" + getMovieTitle(indexRow) + ", " + getMoreMovieURL(indexRow);
 }
 
 public String getRowIDForMovieTitle (String movieTitle) {
	 SQLiteStatement stmtInsert = db.compileStatement("SELECT rowid FROM movie_information2 WHERE movieTitle = " + "'" + movieTitle + "'");
	 return stmtInsert.simpleQueryForString();
 }

}

